/*----------------------------------------\ | Save the excel sheet, if a path and file name is provided, then the| | function save the current excel spread sheet to the directory using| | the filename provided; otherwise it save the current spread sheet | | to the excel default directory using the default name. | |-------------------------------------------| |--------------------------------------------------------------------| |---------------------------| | Argument: | | filepath: A correct path with a excel filename plus excel | | extension; | | excelref: the excel reference; not necessary; default is | | "excelsys"; | |-----------------------------| |--------------------------------------------------------------------| |---------------------------------------| | Example: %excelsave(c:\temp\temp.xls); | | Usage: %excelsave(filepath, excelref); | \----------------------------------------*/ %macro excelsave/parmbuff; /*--------------------------------------------\ | Copy Right: Duo Zhou; | | Created: 6-15-2001 10:54pm; | | Modified: 1-15-2002 8:47pm; | | Purpose: Save the excel spread sheet; | \--------------------------------------------*/ %local fref wsid rc num fileref filepath excelref; %let num=1; %let fileref=%qscan(&syspbuff,&num,%str((),""'')); %let fref=; %let wsid=0; %if %words(&syspbuff, dlm=%quote(,()))>1 %then %let fref=%qscan(&syspbuff,-1,%str(,()''""= )); %else %let fref=excelsys; %if (&fref ne ) %then %let wsid=%sysfunc(fopen(&fref,o,132,e)); %if &wsid %then %do; %let rc=%sysfunc(fclose(&wsid)); DATA _NULL_; FILE &fref; %if &fileref ne %then %do; %if (%substr(%upcase(&fileref),%eval(%length(&fileref)-3),4) ne .XLS) %then %let fileref=&fileref..xls; %put --> Note: File will be saved as "&fileref". ; str='[SAVE.AS("'||trim(left("&fileref"))||'")]'; PUT str; %end; %else %do; PUT '[SAVE]'; %end; RUN; %end; %else %put ==> Alert! Incorrect window reference "&fref", or window "&fref" isn%str(%')t open.; %mend excelsave;